/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import com.mysql.jdbc.Statement;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.print.attribute.DateTimeSyntax;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.jasper.tagplugins.jstl.core.Set;

/**
 *
 * @author Bryan
 */
public class Createloan extends HttpServlet {


    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    /**
     * Inserts a new client, comaker and loan 
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        
        Connection con = DBConnector.connect();
        ResultSet rs;
        int id=0;
        String terms1="";
        try{
       
        String agentcode = request.getParameter("agentcode");
        String lastname = request.getParameter("lastname");
        String firstname = request.getParameter("firstname");
        String middlename = request.getParameter("middlename");
        String street = request.getParameter("street");
        String city = request.getParameter("city");
        String address = request.getParameter("address");
        String zip = request.getParameter("zip");
        String unit = request.getParameter("unit");
        String status = request.getParameter("status");
        String rank = request.getParameter("rank");
        String service = request.getParameter("service");
        String bday = request.getParameter("bday");
        String children = request.getParameter("children");
        String spouse = request.getParameter("spouse");
        String contact = request.getParameter("contact");
        String clienttype = request.getParameter("clienttype");
        String subtype = request.getParameter("subtype");
        String loantype = request.getParameter("loantype");
        
                PreparedStatement ps = con.prepareStatement
                        ("insert into quicklenddb.client(agentcode,lastname,firstname,middlename,birthday,street,city,address,"
                        + "zip,unit,status,rank,service,children,spouse,contact,clienttype,subtype,loantype)"
                        + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                
                ps.setString(1, agentcode);
                ps.setString(2, lastname);
                ps.setString(3, firstname);
                ps.setString(4, middlename);
                ps.setString(5, bday);
                ps.setString(6, street);
                ps.setString(7,city);
                ps.setString(8, address);
                ps.setString(9,zip);
                ps.setString(10,unit);
                ps.setString(11,status);
                ps.setString(12,rank);
                ps.setString(13, service);
                ps.setString(14,children);
                ps.setString(15,spouse);
                ps.setString(16,contact);
                ps.setString(17,clienttype);
                ps.setString(18,subtype);
                ps.setString(19,loantype);
                
                ps.executeUpdate();
                PreparedStatement ps4= con.prepareStatement("select max(last_insert_id()) from client");
                ResultSet rs1 = ps4.executeQuery();
                while(rs1.next()){
                      id = rs1.getInt(1);
                      
                }
            
                String c_lastname = request.getParameter("c_lastname");
                String c_firstname = request.getParameter("c_firstname");
                String c_middlename = request.getParameter("c_middlename");
                String c_street = request.getParameter("c_street");
                String c_city = request.getParameter("c_city");
                String c_address = request.getParameter("c_address");
                String c_zip = request.getParameter("c_zip");
                String c_bday = request.getParameter("c_bday");
                String c_children = request.getParameter("c_children");
                String c_status = request.getParameter("c_status");
                String c_contact = request.getParameter("c_contact");
               
                PreparedStatement ps1 = con.prepareStatement("insert into quicklenddb.comaker"
                        + "(clientID,c_lastname,c_firstname,c_mname,birthday,street,city,address,zip,status,children,contact)"
                        + "values(?,?,?,?,?,?,?,?,?,?,?,?)");
                ps1.setInt(1, id);
                ps1.setString(2, c_lastname);
                ps1.setString(3, c_firstname);
                ps1.setString(4, c_middlename);
                ps1.setString(5, c_bday);
                ps1.setString(6, c_street);
                ps1.setString(7, c_city);
                ps1.setString(8, c_address);
                ps1.setString(9, c_zip);
                ps1.setString(10, c_status);
                ps1.setString(11,c_children);
                ps1.setString(12,c_contact);
                ps1.executeUpdate();
                
                String clienttype1= request.getParameter("clienttype");
                String subtype1=request.getParameter("subtype");
                String loantype1=request.getParameter("loantype");
                String term1 = request.getParameter("term");
                PreparedStatement ps3 = con.prepareStatement("Select termID from quicklenddb.terms where clienttype=? and loantype=? and subtype=? and terms=?");
                ps3.setString(1,clienttype1);
                ps3.setString(2,loantype1);
                ps3.setString(3,subtype1);
                ps3.setString(4,term1);
                rs = ps3.executeQuery();
                
                while(rs.next()){
                     terms1 = rs.getString(1);
                     
                     
                }
                String pn = request.getParameter("pnmonth");
                String terms2 = request.getParameter("term");
                String releasedate = request.getParameter("releasedate");
                String collectdate = request.getParameter("collectdate");
                String loandate = request.getParameter("loandate");
                String checkdate = request.getParameter("checkdate");
                String checkname = request.getParameter("checkname");
                String checkno = request.getParameter("checkno");
                String bank = request.getParameter("bankname");
                String unrelease = request.getParameter("unreleasedate");
                String uncollect = request.getParameter("uncollectdate");
                String daterelease = request.getParameter("daterelease");
                PreparedStatement ps2= con.prepareStatement("insert into quicklenddb.loan "
                        + "(clientID,termsID,pnmonth,loan_amount,loan_date,collect_date,"
                        + "released_date,unrelease_date,uncollect_date,check_date,bank_name,checkname,"
                        + "check_no,status,paid,date_released) values(?,?,?,?,?,?,?,?,?,?,?,?,?,'Pending','Not Paid',?)");
                   
                   ps2.setInt(1, id);
                   ps2.setString(2, terms1);
                   ps2.setString(3, pn);
                   ps2.setFloat(4, Float.parseFloat(String.valueOf(pn))*Float.parseFloat(String.valueOf(terms2)));
                   ps2.setString(5, loandate);
                   ps2.setString(6, collectdate);
                   ps2.setString(7, releasedate);
                   ps2.setString(8, unrelease);
                   ps2.setString(9, uncollect);
                   ps2.setString(10, checkdate);
                   ps2.setString(11, bank);
                   ps2.setString(12, checkname);
                   ps2.setString(13, checkno);
                   ps2.setString(14,daterelease);
                   ps2.executeUpdate();
                 
                response.sendRedirect("LoanServlet");
            
            
        }catch(SQLException e)
        {
            System.out.println(e); 
            if(terms1==null||terms1==""){
                response.sendRedirect("noexistingterms.jsp");
            }else{
                response.sendRedirect("fail.jsp");
            }
            //Logger.getLogger(RegisterServlet.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}
